from autil.db_util import DB


def read_4_code():
    """
        do for multi row such as

        保单号
        1111
        2222
        3333
        """
    rows = []
    with open(file=FILE_PATH, mode="r", encoding="utf-8") as fr:
        for row in fr.readlines():
            row = row.strip()
            if row == '':
                continue
            rows.append(row)

    # 第一行是字段名, 从第二行开始
    # return rows[1:]
    print(len(rows))
    return rows


def read_2_list():
    '''
    do for rows such as
    保单号
    1111
    2222
    3333
    '''

    rows = []

    with open(file=FILE_PATH, mode="r", encoding="utf-8") as fr:
        for row in fr.readlines():
            if row == '' or row.strip() == '': continue
            str_split = ' '
            # str_split = ','

        while True:
            line = fr.readline()
            # print('{} -'.format(line_number))

            if line == '':
                continue

            line = line.replace('"', '')
            line = line.replace('\t', '')
            line = line.replace('\n', '')
            line = line.replace(' ', '')

            if line != '':
                rows.append(line)

    # print(len(rows))
    return rows[1:]


def del_table():
    db.set(f'''DELETE FROM {TABLE_NAME}''')
    get_table_length()


def get_table_length():
    count = db.get(f'''SELECT COUNT(1) FROM {TABLE_NAME}''')[0][0]
    print(f'len({TABLE_NAME}) = {count}')


if __name__ == '__main__':
    FILE_PATH = "./chdrnum.txt"
    TABLE_NAME = 'TEMP_ID_CODE'

    db = DB()

    # ⭐⭐⭐
    db.db2_connect()
    # db.mssql_connect()

    a = 0
    if a == 0:
        sql_multi = f'''INSERT INTO {TABLE_NAME}(ID, CODE) VALUES(?,?)'''  # db
        ## sql_multi = f'''INSERT INTO {TABLE_NAME}(ID, CODE) VALUES(%d,%s)'''  # mssql

        # sql = f''' DROP TABLE {TABLE_NAME} '''
        # sql = f''' CREATE TABLE {TABLE_NAME} ( ID INT PRIMARY KEY NOT NULL, CODE VARCHAR(256) NOT NULL ) '''

        del_table()

        rows = read_4_code()
        rows = [(i, e) for i, e in enumerate(rows)]
        db.set_multi(sql_multi, rows)

        print(get_table_length())

        input("PAUSE WAIT FOR DEL")
        input("PAUSE WAIT FOR DEL TOO")

    del_table()
